package com.andy.dao;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.andy.dao.base.BaseHibernateDAO;
import com.andy.entity.ExamScore;
import com.andy.vo.ExamScoreVO;

@Repository
public class ExamScoreDAO extends BaseHibernateDAO<ExamScore> implements
		IExamScoreDAO {

	/**
	 * 根据用户ＩＤ和科目ＩＤ读取成绩
	 */
	@Override
	public ExamScore getScore(String userId, Integer subId) {
		String hql = " FROM ExamScore es WHERE es.userId = :userId AND es.subjectId = :subjectId ";
		return (ExamScore) this.createQuery(hql)
					.setString("userId", userId)
					.setInteger("subjectId", subId)
					.uniqueResult();
	}
	
	@Override
	public ExamScoreVO getScoreById(Integer scoreId) {
		StringBuilder hql = new StringBuilder();
		hql.append(" select user.userName as userName, sub.name as subjectName, es.examTime as examTime, es.score as score ");
		hql.append(" from ExamScore es, ExamUser user, ExamSubject sub ");
		hql.append(" where es.userId = user.userId ");
		hql.append(" and es.subjectId = sub.subId ");
		hql.append(" and es.id = :scoreId ");
		
		return (ExamScoreVO) this.createQuery(hql.toString())
					.setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class))
					.setInteger("scoreId", scoreId)
					.uniqueResult();
		
		
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<ExamScoreVO> getAllScore(Map<String, String> conditionMap) {
		StringBuilder hql = new StringBuilder();
		hql.append(" select es.id as id, user.userId as userId, user.userName as userName, sub.subId as subjectId, sub.name as subjectName, es.examTime as examTime, es.score as score ");
		hql.append(" from ExamScore es, ExamUser user, ExamSubject sub ");
		hql.append(" where es.userId = user.userId ");
		hql.append(" and es.subjectId = sub.subId ");
		
		// 当条件为空时，相当于查询全部
		if (conditionMap != null) {
			Iterator<Entry<String, String>> it = conditionMap.entrySet().iterator();
			while (it.hasNext()) {
				Entry<String, String> currentCondition = it.next();
				// 当有指定类型的查询条件时，按条件查询
				if ("subjectName".equals(currentCondition.getKey())) {
					hql.append(" and sub.name like '%" + currentCondition.getValue() + "%' " );
				} else if ("userId".equals(currentCondition.getKey())) {
					hql.append(" and user.userId like '%" + currentCondition.getValue() + "%' " );
				} else if ("userName".equals(currentCondition.getKey())) {
					hql.append(" and user.userName like '%" + currentCondition.getValue() + "%' " );
				} else if ("examTime".equals(currentCondition.getKey())) {
					if (currentCondition.getValue() != null) {
						hql.append(" and DATE_FORMAT(es.examTime, '%Y-%m-%d %H:%i') like '%" + currentCondition.getValue() + "%' " );
					}
				}
			}
		}
		return this.createQuery(hql.toString()).setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class)).list();
			
	}

}
